package com.tskj.mh.badminton.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.tskj.mh.badminton.dao.mysql.DbUtilitys;
import com.tskj.mh.badminton.utility.ModuleType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @author LeonSu
 */
@Component
public class BadmintonDAO {

    private final String MODULE_TYPE = String.valueOf(ModuleType.YU_MAO_QIU.ordinal());

    @Autowired
    private DbUtilitys dbUtilitys;

    public JSONArray getBadmintonRoomList() {
        String sql = "select badmintonid id, bname name, sortnum from badminton where used=1 order by bname";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public JSONArray getTimeAndCountList(String hairId, String date) {
        String sql = "select badminton_reserve_time.tid,badminton_reserve_time.starttime, badminton_reserve_time.endtime " +
                ", badminton.bname, (badminton_reserve_time.bnum - ifnull(a.count, 0)) bnum  from badminton_reserve_time " +
                "left join badminton on badminton.badmintonid=? " +
                "left join (select starttime, bname, count(*) as count from badminton_order where startdate=? and orderstate='0' group by bname, starttime) a on cast(a.starttime as char(8)) = cast(badminton_reserve_time.starttime as char(8)) and a.bname=badminton.bname " +
                "where badminton_reserve_time.badmintonid=? and badminton_reserve_time.used=1 order by starttime";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, hairId, date, hairId);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    public String getBadmintonRoomId(String badmintonRoom) {
        String sql = "select badmintonid from badminton where bname=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, badmintonRoom);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("badmintonid");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getRoomSortNo(String hairId, String name) {
        String sql = "select sortnum from badminton where badmintonid=? and bname=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, hairId, name);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getTimeSortNo(String badmintonId, String beginTime, String endTime) {
        String sql = "select sortnum from badminton_reserve_time where badmintonid=? and starttime=? and endtime=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, badmintonId, beginTime, endTime);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getTimeSortNo(String badmintonId, String timeId) {
        String sql = "select sortnum from badminton_reserve_time where badmintonid=? and tid=? limit 1";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, badmintonId, timeId);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public String getMaxOrderNo(String hairName, String startDate, String startTime, String endTime) {
        String sql = "select (ifnull(max(sortnum), 0) + 1) sortnum from badminton_order where bname=? and starttime=? and endtime=? and startdate=? for update";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, hairName, startTime, endTime, startDate);
        if (list == null || list.size() == 0) {
            return "";
        }
        Object obj = list.get(0).get("sortnum");
        if (obj == null) {
            return "";
        }
        return obj.toString();
    }

    public boolean isClosed(String badmintonRoom, String startTime, String endTime) {
        String sql = "SELECT used FROM badminton_reserve_time WHERE badmintonid=? and starttime=? and endtime=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, badmintonRoom, startTime, endTime);
        if (list == null || list.size() == 0) {
            return true;
        }
        Object value = list.get(0).get("used");
        if (value == null || "".equals(value.toString()) || !Boolean.valueOf(value.toString())) {
            return true;
        }
        return false;
    }

    /**
     * 检查此场地的此日期的此时间段是否已订满
     *
     * @param badmintonRoom
     * @param startTime
     * @param endTime
     * @param status
     * @param startDate
     * @return
     */
    public boolean canSave(String badmintonRoom, String startTime, String endTime, int status, String startDate) {
        String sql = "select (badminton_reserve_time.bnum - ifnull(a.count, 0)) emptycount from badminton_reserve_time left join " +
                "(select count(*) count, bname,starttime,endtime from badminton_order where startdate=? and bname=? and starttime=? and endtime=? and orderstate=0 for update" +
                ") a on a.starttime = badminton_reserve_time.starttime and a.endtime=badminton_reserve_time.endtime " +
                "right join badminton on badminton.badmintonid=badminton_reserve_time.badmintonid and badminton.bname=? " +
                "where badminton_reserve_time.starttime=? and badminton_reserve_time.endtime=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, startDate, badmintonRoom, startTime, endTime, badmintonRoom, startTime, endTime);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("emptycount");
        if (value == null || "".equals(value.toString()) || 0 >= Integer.valueOf(value.toString())) {
            return false;
        }
        return true;
    }

    public void save(String id, String studentName, String sex, int maxOrderNo, String badmintonRoom, String startTime, String endTime, int status, String code, String startDate, String adUserName, int studentCount, String xueYuan) throws Exception {
        String sql = "insert into badminton_order (orderid, userid, sortnum, bname, starttime, endtime, orderstate, qrcode, startdate, adusername, createdate, pcount, xueyuan) " +
                "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, ?)";
        dbUtilitys.execSQLWithTran(sql, id, studentName, maxOrderNo, badmintonRoom, startTime, endTime, status, code, startDate, adUserName, studentCount, xueYuan);
    }

    public JSONObject getStartTime() {
        JSONObject ret;
        String sql = "select starttime as start, endtime as end from modules where id=2";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql);
        if (list == null || list.size() == 0) {
            ret = new JSONObject();
            ret.put("start", "08:00:00");
            ret.put("end", "23:59:59");
            return ret;
        }
        return JSONObject.parseObject(JSONObject.toJSONString(list.get(0)));
    }

    public boolean hasOrder(String adUserName, String date) {
        return getOrderByAdUserName(adUserName, date) > 1;
    }

    public boolean hasTableTennisOrder(String adUserName, String date, String startTime) {
        return getTennisOrderByAdUserName(adUserName, date, startTime) != 0;
    }

    public int getOrderByAdUserName(String adUserName, String date) {
        String sql = "select ifnull(count(*), 0) count from badminton_order where adusername=? and createdate between ? and ? and orderstate='0' for update";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, date + " 00:00:00", date + " 23:59:59");
        if (list == null || list.size() == 0) {
            return 0;
        }
        return Integer.valueOf(list.get(0).get("count").toString());
    }

    public int getTennisOrderByAdUserName(String adUserName, String date, String startTime) {
        String sql = "select ifnull(count(*), 0) count from tabletennis_order where orderstate=0 and adusername=? and startdate=? and starttime=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName, date, startTime);
        if (list == null || list.size() == 0) {
            return 0;
        }
        return Integer.valueOf(list.get(0).get("count").toString());
    }

    /**
     * 获取未完成订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getUnFinishedOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, '' xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate, " +
                "date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now() canCancel, " +
                "str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now() canConfirm " +
                "from badminton_order " +
                "where adusername=? and orderstate=0 " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取已完成订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getFinishedOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, '' xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from badminton_order " +
                "where adusername=? and orderstate=1 " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取全部订单
     *
     * @param adUserName
     * @return
     */
    public JSONArray getAllOrder(String adUserName) {
        String sql = "select orderid id, " + MODULE_TYPE + " type, bname address, startdate date, concat(substr(starttime, 1, 5),' - ', substr(endtime, 1, 5)) time, qrcode code, orderstate status, '' xueyuan, date_format(createdate, '%Y-%m-%d %H:%i:%S') createdate from badminton_order " +
                "where adusername=? " +
                "order by createdate desc";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, adUserName);
        return JSONArray.parseArray(JSONArray.toJSONString(list));
    }

    /**
     * 获取订单是否允许确认
     *
     * @param orderId
     * @return 0：不能确认，1：可以确认
     */
    public Boolean getCanConfirm(String orderId) {
        String sql = "select str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now() canConfirm from badminton_order where orderid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, orderId);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("canConfirm");
        if (value == null || !"1".equals(value.toString())) {
            return false;
        }
        return true;

    }

    public void confirmOrder(String adUserName, String orderId) throws Exception {
        String sql = "update badminton_order set orderstate=1, ctime=now() where orderid=? and adusername=? and str_to_date(concat(startdate, endtime),'%Y-%m-%d%T') < now()";
        dbUtilitys.execSQLWithTran(sql, orderId, adUserName);
    }

    /**
     * 获取订单是否允许取消
     *
     * @param orderId
     * @return 0：不能取消，1：可以取消
     */
    public Boolean getCanCancel(String orderId) {
        String sql = "select date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now() canCancel from badminton_order where orderid=?";
        List<Map<String, Object>> list = dbUtilitys.execSQL(sql, orderId);
        if (list == null || list.size() == 0) {
            return false;
        }
        Object value = list.get(0).get("canCancel");
        if (value == null || !"1".equals(value.toString())) {
            return false;
        }
        return true;
    }

    public void cancelOrder(String adUserName, String orderId) throws Exception {
        String sql = "update badminton_order set orderstate=2, ctime=now() where orderid=? and adusername=? and date_sub(str_to_date(concat(startdate, starttime),'%Y-%m-%d%T'), interval 30 minute) > now()";
        dbUtilitys.execSQLWithTran(sql, orderId, adUserName);
    }

}
